package com.junzhiit.nengji.shop.dao.impl;

import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;

import com.bainaeco.framework.base.dao.AbstractBaseDAO;
import com.bainaeco.framework.common.jdbc.StatementParameter;
import com.bainaeco.framework.enums.IsDelete;
import com.bainaeco.framework.util.DateUtil;
import com.junzhiit.nengji.shop.bo.HomeHeaderInfoBO;
import com.junzhiit.nengji.shop.bo.PayHistoryBO;
import com.junzhiit.nengji.shop.dao.PayHistoryDAO;
import com.junzhiit.nengji.shop.model.PayHistory;
import com.junzhiit.nengji.shop.model.enums.Is;

@Repository
public class PayHistoryDAOImpl extends AbstractBaseDAO<PayHistory> implements PayHistoryDAO{

	@Override
	public List<PayHistoryBO> queryPayHistory(PayHistoryBO phb,Integer pageNumber, Integer maxPageRow) {
		StringBuffer sql=new StringBuffer();
		StatementParameter params=new StatementParameter();
		sql.append("select ph.*,u.mobile_phone userMobile,u.nick_name userName,o.pay_time payTime,o.id orderId");
		sql.append(" from pay_history ph");
		sql.append(" inner join user u on ph.user_id = u.id");
		sql.append(" inner join orders o on o.shop_order_number = ph.shop_order_number");
		sql.append(" where ph.is_delete=? and o.pay_time between ? and ?");
		params.setInt(IsDelete.NO.toIntValue());
		params.setDate(phb.getStartTime());
		params.setDate(phb.getEndTime());
		if(StringUtils.isNotBlank(phb.getShopId())){
			sql.append(" and ph.shop_id=?");
			params.setString(phb.getShopId());
		}else{
			sql.append(" and ph.shop_id in (select s.id from shop s where s.is_delete=? and s.chain_id=?)");
			params.setInt(IsDelete.NO.toIntValue());
			params.setString(phb.getChainId());
		}
		sql.append(" order by o.pay_time desc");
		
		if(pageNumber!=null&&maxPageRow!=null){
			return super.queryForPagingList(sql.toString(), PayHistoryBO.class, params,pageNumber,maxPageRow);
		}else{
			return super.queryForList(sql.toString(), PayHistoryBO.class, params);
		}
	}

	@Override
	public int getTotalCount(PayHistoryBO phb) {
		StringBuffer sql=new StringBuffer();
		StatementParameter params=new StatementParameter();
		sql.append("select count(ph.id)");
		sql.append(" from pay_history ph");
		sql.append(" inner join user u on ph.user_id = u.id");
		sql.append(" inner join orders o on o.shop_order_number = ph.shop_order_number");
		sql.append(" where ph.is_delete=? and o.pay_time between ? and ?");
		params.setInt(IsDelete.NO.toIntValue());
		params.setDate(phb.getStartTime());
		params.setDate(phb.getEndTime());
		if(StringUtils.isNotBlank(phb.getShopId())){
			sql.append(" and ph.shop_id=?");
			params.setString(phb.getShopId());
		}else{
			sql.append(" and ph.shop_id in (select s.id from shop s where s.is_delete=? and s.chain_id=?)");
			params.setInt(IsDelete.NO.toIntValue());
			params.setString(phb.getChainId());
		}	
		return super.queryForInt(sql.toString(),params);
	}

	@Override
	public List<PayHistoryBO> queryPayRanking(PayHistoryBO payHistoryBO,Integer pageNumber, Integer maxPageRow) {
		StringBuffer sql = new StringBuffer();
		sql.append("select u.id userId,sum(ph.money) totalMoney,u.mobile_phone userMobile,u.nick_name userName");
		sql.append(" from pay_history ph");
		sql.append(" inner join user u on ph.user_id = u.id");
		sql.append(" inner join orders o on o.shop_order_number = ph.shop_order_number");
		sql.append(" where ph.shop_id = ? ");
		
		StatementParameter params=new StatementParameter();
		params.setString(payHistoryBO.getShopId());
		
		if(payHistoryBO.getStartTime() != null){
			sql.append(" and o.pay_time >= ? ");
			params.setDate(DateUtil.getDateStart(payHistoryBO.getStartTime()));
		}
		if(payHistoryBO.getEndTime() != null){
			sql.append(" and o.pay_time <= ? ");
			params.setDate(DateUtil.getDateEnd(payHistoryBO.getEndTime()));
		}
		
		sql.append(" group by u.id");
		sql.append(" order by totalMoney desc");
		
		if(pageNumber!=null&&maxPageRow!=null){
			return super.queryForPagingList(sql.toString(), PayHistoryBO.class, params,pageNumber,maxPageRow);
		}else{
			return super.queryForList(sql.toString(), PayHistoryBO.class, params);
		}
	}
	
	@Override
	public int getPayRankingTotalCount(PayHistoryBO payHistoryBO) {
		StringBuffer sql=new StringBuffer();
		sql.append("select count(distinct u.id)");
		sql.append(" from pay_history ph");
		sql.append(" inner join user u on u.id = ph.user_id");
		sql.append(" inner join orders o on o.shop_order_number = ph.shop_order_number");
		sql.append(" where ph.shop_id = ? ");
		
		StatementParameter params=new StatementParameter();
		
		params.setString(payHistoryBO.getShopId());
		
		if(payHistoryBO.getStartTime() != null){
			sql.append(" and o.pay_time >= ? ");
			params.setDate(DateUtil.getDateStart(payHistoryBO.getStartTime()));
		}
		if(payHistoryBO.getEndTime() != null){
			sql.append(" and o.pay_time <= ? ");
			params.setDate(DateUtil.getDateEnd(payHistoryBO.getEndTime()));
		}
		
		return super.queryForInt(sql.toString(),params);
	}

	@Override
	public List<PayHistoryBO> queryUserPayHistory(String shopId,String userId,Integer pageNumber, Integer maxPageRow) {
		StringBuffer sql = new StringBuffer();
		sql.append("select ph.*,u.mobile_phone userMobile,u.nick_name userName,o.pay_time payTime,o.id orderId");
		sql.append(" from pay_history ph");
		sql.append(" inner join user u on ph.user_id = u.id");
		sql.append(" inner join orders o on o.shop_order_number = ph.shop_order_number");
		sql.append(" where ph.shop_id = ? and ph.user_id = ?");
		sql.append(" order by o.pay_time desc");
		StatementParameter params = new StatementParameter();
		params.setString(shopId);
		params.setString(userId);
		
		if(pageNumber!=null&&maxPageRow!=null){
			return super.queryForPagingList(sql.toString(), PayHistoryBO.class, params,pageNumber,maxPageRow);
		}else{
			return super.queryForList(sql.toString(), PayHistoryBO.class, params);
		}
	}

	@Override
	public int getUserPayHistoryTotalCount(String shopId, String userId) {
		StringBuffer sql = new StringBuffer();
		sql.append("select count(*)");
		sql.append(" from pay_history ph");
		sql.append(" inner join user u on ph.user_id = u.id");
		sql.append(" inner join orders o on o.shop_order_number = ph.shop_order_number");
		sql.append(" where ph.shop_id = ? and ph.user_id = ?");
		StatementParameter params = new StatementParameter();
		params.setString(shopId);
		params.setString(userId);
		return super.queryForInt(sql.toString(),params);
	}

	@Override
	public HomeHeaderInfoBO getShopData(String chainId) {
		StringBuffer sql = new StringBuffer();
		sql.append("select count(ph.id) totalPay,sum(ph.money) totalTransaction from pay_history ph");
		sql.append(" inner join shop s on s.id = ph.shop_id");
		sql.append(" inner join chain c on c.id = s.chain_id");
		sql.append(" where ph.is_delete = ? and c.id = ?");
		StatementParameter params = new StatementParameter();
		params.setInt(Is.NO.toIntValue());
		params.setString(chainId);
		
		return query(sql.toString(),HomeHeaderInfoBO.class,params);
	}
	
}
